#!/bin/bash

# 定义变量：数据同步日期，表示同步哪一条日志数据
# 第1、执行shell脚本时，传递参数
# 第2、如果没有传递参数，同步前一天数据
if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d "-1 days" +%F`
fi

# 加载数据语句
DIM_MD_AREAS_SQL="
DROP FUNCTION IF EXISTS convert_geohash ;
CREATE FUNCTION convert_geohash AS 'net.bwie.jtp.app.udf.ConvertGeoHash'
    USING JAR 'hdfs://node101:8020/warehouse/app_jars/jtp_log_warehouse-1.0-SNAPSHOT.jar';
WITH tmp_area AS (
    SELECT
        ID, AREANAME, PARENTID, SHORTNAME, LEVEL, FLAG, WGS84_LNG, WGS84_LAT, GCJ02_LNG, GCJ02_LAT, BD09_LNG, BD09_LAT
    FROM lx_jtp_app_ods.ods_md_areas
    WHERE dt = '${data_date}'
)
INSERT OVERWRITE TABLE lx_jtp_app_dim.dim_md_areas
    PARTITION (dt
        ='${data_date}')
SELECT
    t3.ID,t3.province,t3.city,t3.district,tt3.street
     ,tt3.BD09_LAT,tt3.BD09_LNG
    ,lx_jtp_app_dim.convert_geohash(BD09_LAT,BD09_LNG,6) as geo_hash
FROM (
         SELECT
             tt2.ID,t2.province,t2.city,tt2.district
         FROM (
                  SELECT
                      tt1.ID,t1.province,tt1.city
                  FROM(
                          SELECT
                              ID,AREANAME AS province
                          FROM lx_jtp_app_ods.ods_md_areas
                          WHERE dt='${data_date}' AND LEVEL=1
                      )t1
                          LEFT JOIN (
                      SELECT
                          ID,AREANAME AS city,PARENTID
                      FROM lx_jtp_app_ods.ods_md_areas
                      WHERE dt='${data_date}' AND LEVEL=2
                  )tt1 ON t1.ID=tt1.PARENTID
              )t2
                  LEFT JOIN (
             SELECT
                 ID,AREANAME AS district,PARENTID
             FROM lx_jtp_app_ods.ods_md_areas
             WHERE dt='${data_date}' AND LEVEL=3
         )tt2 ON t2.ID=tt2.PARENTID
     )t3
         LEFT JOIN (
    SELECT
        ID,AREANAME AS street,PARENTID,BD09_LAT,BD09_LNG
    FROM lx_jtp_app_ods.ods_md_areas
    WHERE dt='${data_date}' AND LEVEL=4
)tt3 ON t3.ID=tt3.PARENTID
WHERE tt3.BD09_LAT IS NOT NULL AND tt3.BD09_LNG IS NOT NULL;
"

# 执行SQL语句
/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${DIM_MD_AREAS_SQL}"

